﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Text.RegularExpressions;
using System.Web.UI;

public class TSQL
{
    public static int ExecuteNonQuery(string sql, params object[] s)
    {
        return ExecuteNonQueryWithOther(sql, "", null, s);
    }
    public static int ExecuteNonQuery(string sql, string[] p, params object[] s)
    {
        return ExecuteNonQueryWithOther(sql, "", p, s);
    }
    public static int ExecuteNonQueryWithOther(string sql, string connstr, params object[] s)
    {
        return ExecuteNonQueryWithOther(sql, connstr, null, s);
    }
    public static int ExecuteNonQueryWithOther(string sql, string connstr, string[] p, params object[] s)
    {
        int result = 0;
        using (SQLM sm = new SQLM(connstr))
        {
            result = sm.ExecuteNonQuery(sql, p, s);
        }
        return result;
    }

    public static int ExecuteNonQueryWithTran(string sql, params object[] s)
    {
        return ExecuteNonQueryWithTranWithOther(sql, "", null, s);
    }
    public static int ExecuteNonQueryWithTran(string sql, string[] p, params object[] s)
    {
        return ExecuteNonQueryWithTranWithOther(sql, "", p, s);
    }
    public static int ExecuteNonQueryWithTranWithOther(string sql, string connstr, params object[] s)
    {
        return ExecuteNonQueryWithTranWithOther(sql, connstr, null, s);
    }
    public static int ExecuteNonQueryWithTranWithOther(string sql, string connstr, string[] p, params object[] s)
    {
        int result = 0;
        using (SQLM sm = new SQLM(connstr))
        {
            sm.BeginTransaction();
            result = sm.ExecuteNonQuery(sql, p, s);
            sm.Commit();
        }
        return result;
    }

    public static object ExecuteScalar(string sql, params object[] s)
    {
        return ExecuteScalarWithOther(sql, "", null, s);
    }
    public static object ExecuteScalar(string sql, string[] p, params object[] s)
    {
        return ExecuteScalarWithOther(sql, "", p, s);
    }
    public static object ExecuteScalarWithOther(string sql, string connstr, params object[] s)
    {
        return ExecuteScalarWithOther(sql, connstr, null, s);
    }
    public static object ExecuteScalarWithOther(string sql, string connstr, string[] p, params object[] s)
    {
        object result = null;
        using (SQLM sm = new SQLM(connstr))
        {
            result = sm.ExecuteScalar(sql, p, s);
        }
        return result;
    }

    public static DataRow ExecDr(string sql, params object[] s)
    {
        return ExecDrWithOther(sql, "", null, s);
    }
    public static DataRow ExecDr(string sql, string[] p, params object[] s)
    {
        return ExecDrWithOther(sql, "", p, s);
    }
    public static DataRow ExecDrWithOther(string sql, string connstr, params object[] s)
    {
        return ExecDrWithOther(sql, connstr, null, s);
    }
    public static DataRow ExecDrWithOther(string sql, string connstr, string[] p, params object[] s)
    {
        DataRow dr = null;
        using (SQLM sm = new SQLM(connstr))
        {
            dr = sm.ExecDr(sql, p, s);
        }
        return dr;
    }

    public static DataSet ExecDs(string sql, params object[] s)
    {
        return ExecDsWithOther(sql, "", null, s);
    }
    public static DataSet ExecDs(string sql, string[] p, params object[] s)
    {
        return ExecDsWithOther(sql, "", p, s);
    }
    public static DataSet ExecDsWithOther(string sql, string connstr, params object[] s)
    {
        return ExecDsWithOther(sql, connstr, null, s);
    }
    public static DataSet ExecDsWithOther(string sql, string connstr, string[] p, params object[] s)
    {
        DataSet ds = new DataSet();
        using (SQLM sm = new SQLM(connstr))
        {
            ds = sm.ExecDs(sql, p, s);
        }
        return ds;
    }

    public static DataTable ExecDt(string sql, params object[] s)
    {
        return ExecDtWithOther(sql, "", null, s);
    }
    public static DataTable ExecDt(string sql, string[] p, params object[] s)
    {
        return ExecDtWithOther(sql, "", p, s);
    }
    public static DataTable ExecDtWithOther(string sql, string connstr, params object[] s)
    {
        return ExecDtWithOther(sql, connstr, null, s);
    }
    public static DataTable ExecDtWithOther(string sql, string connstr, string[] p, params object[] s)
    {
        DataSet ds = new DataSet();
        using (SQLM sm = new SQLM(connstr))
        {
            ds = sm.ExecDs(sql, p, s);
        }
        try
        {
            return ds.Tables[0];
        }
        catch
        {
            return null;
        }
    }

    public static DataSet ExecProc(string procedureName)
    {
        return ExecProcWithOther(procedureName, "", null, null);
    }
    public static DataSet ExecProcWithOther(string procedureName, string connstr)
    {
        return ExecProcWithOther(procedureName, connstr, null, null);
    }
    public static DataSet ExecProc(string procedureName, string[] paramsName, object[] value)
    {
        return ExecProcWithOther(procedureName, "", paramsName, value);
    }
    public static DataSet ExecProcWithOther(string procedureName, string connstr, string[] paramsName, object[] value)
    {
        DataSet ds = new DataSet();
        using (SQLM sm = new SQLM(connstr))
        {
            ds = sm.ExecProc(procedureName, paramsName, value);
        }
        return ds;
    }

    public static DataRow NewRow(string tableName)
    {
        return TSQL.ExecDt("select top 0 * from [" + tableName + "]").NewRow();
    }

    public static int Insert(string tableName, DataRow dr, params string[] primaryKey)
    {
        return TSQL.InsertWithOther(tableName, dr, "", primaryKey);
    }
    public static int InsertWithOther(string tableName, DataRow dr, string connstr, params string[] primaryKey)
    {
        if (primaryKey.Length == 0) primaryKey = new string[] { "id" };
        DataTable dt = dr.Table;
        string sql = "insert into [" + tableName + "](";
        string values = "";
        List<object> list = new List<object>();
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if (!primaryKey.Contains(dt.Columns[i].ColumnName))
            {
                sql += "[" + dt.Columns[i].ColumnName + "],";
                values += "@" + i + ",";
                list.Add(dr[i]);
            }
        }
        sql = sql.Trim(',') + ") values(" + values.Trim(',') + ")";

        return TSQL.ExecuteNonQueryWithOther(sql, connstr, list.ToArray());
    }

    public static int Update(string tableName, DataRow dr, params string[] primaryKey)
    {
        return TSQL.UpdateWithOther(tableName, dr, "", primaryKey);
    }
    public static int UpdateWithOther(string tableName, DataRow dr, string connstr, params string[] primaryKey)
    {
        if (primaryKey.Length == 0) primaryKey = new string[] { "id" };
        DataTable dt = dr.Table;
        string sql = "update [" + tableName + "] set ";
        List<object> list = new List<object>();
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if (!primaryKey.Contains(dt.Columns[i].ColumnName))
            {
                sql += "[" + dt.Columns[i].ColumnName + "] = @" + i + ",";
                list.Add(dr[i]);
            }
        }
        sql = sql.Trim(',');
        for (int i = 0; i < primaryKey.Length; i++)
        {
            if (i == 0)
                sql += " where ";
            else
                sql += " and ";
            sql += "[" + primaryKey[i] + "] = @" + (dt.Columns.Count + i);
            list.Add(dr[primaryKey[i]]);
        }

        return TSQL.ExecuteNonQueryWithOther(sql, connstr, list.ToArray());
    }

    public static int Delete(string tableName, DataRow dr, params string[] primaryKey)
    {
        return TSQL.DeleteWithOther(tableName, dr, "", primaryKey);
    }
    public static int DeleteWithOther(string tableName, DataRow dr, string connstr, params string[] primaryKey)
    {
        if (primaryKey.Length == 0) primaryKey = new string[] { "id" };
        List<object> list = new List<object>();
        string sql = "delete [" + tableName + "]";
        for (int i = 0; i < primaryKey.Length; i++)
        {
            if (i == 0)
                sql += " where ";
            else
                sql += " and ";
            sql += "[" + primaryKey[i] + "] = @" + i;
            list.Add(dr[primaryKey[i]]);
        }

        return TSQL.ExecuteNonQueryWithOther(sql, connstr, list.ToArray());
    }
}
